<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2019/12/11 0011
 * Time: 15:13
 */

namespace application\admin\controller;


/*
 *  表格管理
 */


use application\admin\model\Excel;
use think\Cache;
use think\Db;
use think\Loader;

class ExcelController extends CommonController
{
    public function set(){
        $data = Db::name('host')->select();
        $this->assign('data',$data);
        return $this->fetch();
    }
    public function info(){
        if(request()->isPost()){
            $id = $this->request->param('id');
            $map['hostname'] = $this->request->param('hostname');
            $map['database'] = $this->request->param('database');
            $map['password'] = $this->request->param('password');
            $map['username'] = $this->request->param('username');
            $result = Db::name('host')->where('id',$id)->update($map);
            if ($result){
                $this->success('操作成功', url('set'));
            }
        }
        $id = $this->request->param('id');
        $data = Db::name('host')->where('id',$id)->find();
        $this->assign('info',$data);
        return $this->fetch();
    }
    public function index(){

    //    $lists = $db->table('itemgroup')->paginate(15);
        Cache::rm('data_tables');
      //  print_r($_FILES);
        if (request()->isPost()) {

            $file = request()->file('excel');

            if ($file) {
                $file_types = explode(".", $_FILES ['excel'] ['name']); // ["name"] => string(25) "excel文件名.xls"
                $file_type = $file_types [count($file_types) - 1];//xls后缀
                /*判别是不是.xls文件，判别是不是excel文件*/
                if (strtolower($file_type) != "xls" && strtolower($file_type) != "xlsx") {
                    echo '不是Excel文件，重新上传';
                    die;
                }
            }
            $info = $file->move(ROOT_PATH . 'public' .DS.'uploads'. DS . 'excel');
            if($info){
                $path=ROOT_PATH . 'public' . DS.'uploads'.DS .'excel/'.$info->getSaveName();
                vendor("PHPExcel.PHPExcel");
                $objReader=new \PHPExcel_Reader_Excel2007();
                $objPHPExcel = $objReader->load($path,$encode='utf-8');//获取excel文件
                $sheet = $objPHPExcel->getSheet(0); //激活当前的表
                 $highestRow = $sheet->getHighestRow(); // 取得总行数
                 $highestColumn = $sheet->getHighestColumn(); // 取得总列数

                 $a = 0;
                $arr = array();
                for($i=2;$i<=$highestRow;$i++)
                {
                    //*为什么$i=2? (因为Excel表格第一行应该是姓名，年龄，班级，从第二行开始，才是我们要的数据。)
                    $arr[$a]['groupno'] = $objPHPExcel->getActiveSheet()->getCell("A".$i)->getValue();
                    $arr[$a]['id'] = $objPHPExcel->getActiveSheet()->getCell("B".$i)->getValue();
                    $arr[$a]['typeflg'] = $objPHPExcel->getActiveSheet()->getCell("C".$i)->getValue();
                    $arr[$a]['itemno'] = $objPHPExcel->getActiveSheet()->getCell("D".$i)->getValue();
                    $arr[$a]['qty'] = intval($objPHPExcel->getActiveSheet()->getCell("E".$i)->getValue());
                    $arr[$a]['descript'] = $objPHPExcel->getActiveSheet()->getCell("F".$i)->getValue();
                    $arr[$a]['remarks'] = $objPHPExcel->getActiveSheet()->getCell("G".$i)->getValue();
                    $arr[$a]['groupid'] = $objPHPExcel->getActiveSheet()->getCell("H".$i)->getValue();
                    // 这里的数据根据自己表格里面有多少个字段自行决定
                    $a++;
                }
                //$model =model('Excel');
       /*         $result = $db->table('itemgroup')->insertAll($arr,false);
                if($result){
                    $this->success('操作成功', url('index'));
                }else{
                    $this->success('操作失败，可能键值有重复，请审查后在提交', url('index'));
                }*/

            }
            Cache::set('data_tables',$arr);
            $lists = $arr;
            $this->assign('lists', $lists);
        }

        return $this->fetch();
       // return json($lists);
      //  return json($data);
    }
    public function import(){
        $data_tables = Cache::get('data_tables');
        if(!empty($data_tables)) {
            $host = Db::name('host')->where('id', '1')->find();
            $hostname = $host['hostname'];
            $database = $host['database'];
            $username = $host['username'];
            $password = $host['password'];
            $db = Db::connect("sqlsrv://$username:$password@$hostname/$database#utf8");
            if(!$db){
                $this->error('sqlserver数据连接失败 请确认连接','index');
            }else {
                try {
                   $data = $db->table('itemgroup')->insertAll($data_tables, false);

                } catch (\Exception $e) {  //如书写为（Exception $e）将无效
                  //  echo $e->getMessage();
                    $this->error('操作失败，可能键值有重复，请审查后在提交', url('index'));

                }
                $this->success('导入成功', url('index'));
            }
        }else{
            $this->error('导入的数据为空 请重新上传','index');
        }
    }

}